﻿using Avin.Infrastructure.Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace Avin.Infrastructure.DbContext
{
    public class DbContextBase : IDisposable, IDbContextBase
    {
        private DbConnection _dbConnection;

        private DbCommand _dbCommand;

        private readonly List<CommandObject> _commandObjects = new List<CommandObject>();

        /// <summary>
        /// 执行命令超时时间
        /// </summary>
        private int _commandTimeout = 30;

        public DbProviderType DbProviderType { get; private set; }

        public DbProviderFactory DbProviderFactory { get; private set; }

        public DbContextBase()
        {
            DbProviderType = DbProviderType.SqlServer;
            ChangeDataBase("Password=avin@421324;Persist Security Info=True;User ID=sa;Initial Catalog=Test;Data Source=.;", DbProviderType);
        }

        public DbCommand CreateDbCommand()
        {
            return CreateDbCommand(DbProviderType);
        }

        public DbConnection CreateDbConnection()
        {
            return CreateDbConnection(DbProviderType);
        }

        public DbParameter CreateDbParameter()
        {
            return CreateDbParameter(DbProviderType);
        }

        public DbParameter[] CreateDbParameters(int count = 1)
        {
            return CreateDbParameters(DbProviderType, count);
        }

        public void ChangeDataBase(string connectionString, DbProviderType providerType)
        {
            if (_dbConnection != null)
                SaveChanges();

            if (string.IsNullOrWhiteSpace(connectionString))
                throw new ArgumentException("Argument ConnectionString can not be null or empty");

            DbProviderType = providerType;
            DbProviderFactory = DataBaseProviderFactory.GetDbProviderFactory(DbProviderType);
            if (DbProviderFactory == null)
                throw new ArgumentException("Can't load DbProviderFactory for given value of providerType");

            if (_dbCommand != null)
                _dbCommand.Dispose();

            if (_dbConnection != null)
                _dbConnection.Close();

            _dbConnection = CreateDbConnection(DbProviderType);
            //_dbConnectionString = connectionString;
            _dbConnection.ConnectionString = connectionString;
            _dbCommand = _dbConnection.CreateCommand();
            _dbCommand.CommandTimeout = _commandTimeout;
            _dbConnection.Open();
        }

        public void SetCommandTimeout(int commandTimeout)
        {
            _commandTimeout = commandTimeout;
            _dbCommand.CommandTimeout = _commandTimeout;
        }

        public void InsertUserSQL(int sqlType, string sql, DbParameter[] parameters = null, object source = null)
        {
            if (string.IsNullOrWhiteSpace(sql))
                return;

            _commandObjects.Add(new CommandObject { SQLType = sqlType, SQL = sql, Params = parameters, Source = source });
        }

        public void ClearUserSQL()
        {
            _commandObjects.Clear();
        }

        public int SaveChanges()
        {
            if (_dbConnection.State != ConnectionState.Open)
                _dbConnection.Open();

            if (_commandObjects.Count == 0)
                return 0;

            int iAffect = 0;
            using DbTransaction transaction = _dbConnection.BeginTransaction();
            _dbCommand.Transaction = transaction;
            try
            {
                foreach (var commandObject in _commandObjects)
                {
                    if (commandObject.SQLType == 1)
                    {
                        if (commandObject.Source != null && commandObject.Source is EntityBase<int> @base)
                        {
                            commandObject.SQL += ";select @@IDENTITY;";
                            var obj = ExecuteScalar(commandObject.SQL, commandObject.Params);
                            if (obj != null)
                            {
                                @base.Id = int.Parse(obj.ToString());
                            }
                            iAffect++;
                        }
                        else
                        {
                            iAffect += ExecuteNonQuery(commandObject.SQL, commandObject.Params);
                        }
                    }
                    else
                    {
                        iAffect += ExecuteNonQuery(commandObject.SQL, commandObject.Params);
                    }
                }
                transaction.Commit();
                _commandObjects.Clear();
                return iAffect;
            }
            catch
            {
                transaction.Rollback();
                _commandObjects.Clear();
                _dbCommand.Parameters.Clear();
                throw;
            }
        }

        public virtual void LogError(string sql, Exception ex)
        {
        }

        public void Dispose()
        {
            if (_dbCommand != null)
                _dbCommand.Dispose();

            if (_dbConnection != null)
                _dbConnection.Close();
        }

        public DataSet ExecuteDataSet(string sql)
        {
            return ExecuteDataSet(sql, null, CommandType.Text);
        }

        public DataSet ExecuteDataSet(string sql, DbParameter[] parameters)
        {
            return ExecuteDataSet(sql, parameters, CommandType.Text);
        }

        public DataSet ExecuteDataSet(string sql, DbParameter[] parameters, CommandType commandType)
        {
            try
            {
                InitExecute(sql, parameters, commandType);
                using var adapter = DbProviderFactory.CreateDataAdapter();
                if (adapter == null)
                    return null;
                adapter.SelectCommand = _dbCommand;
                var dataSet = new DataSet();
                adapter.Fill(dataSet);
                return dataSet;
            }
            catch (Exception ex)
            {
                string strErrorSQL = "语句：" + sql + "；";
                if (parameters != null && parameters.Length > 0)
                {
                    strErrorSQL += "参数：";
                    foreach (var p in parameters)
                    {
                        strErrorSQL += p.Value.ToString() + "；";
                    }
                    strErrorSQL += "。";
                }
                LogError(strErrorSQL, ex);
                throw;
            }
        }

        public int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, null, CommandType.Text);
        }

        public int ExecuteNonQuery(string sql, DbParameter[] parameters)
        {
            return ExecuteNonQuery(sql, parameters, CommandType.Text);
        }

        public int ExecuteNonQuery(string sql, DbParameter[] parameters, CommandType commandType)
        {
            try
            {
                InitExecute(sql, parameters, commandType);
                var affectedRows = _dbCommand.ExecuteNonQuery();
                return affectedRows;
            }
            catch (Exception ex)
            {
                string strErrorSQL = "语句：" + sql + "；";
                if (parameters != null && parameters.Length > 0)
                {
                    strErrorSQL += "参数：";
                    foreach (var p in parameters)
                    {
                        strErrorSQL += p.Value.ToString() + "；";
                    }
                    strErrorSQL += "。";
                }
                LogError(strErrorSQL, ex);
                throw;
            }
        }

        public object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, null, CommandType.Text);
        }

        public object ExecuteScalar(string sql, DbParameter[] parameters)
        {
            return ExecuteScalar(sql, parameters, CommandType.Text);
        }

        public object ExecuteScalar(string sql, DbParameter[] parameters, CommandType commandType)
        {
            try
            {
                InitExecute(sql, parameters, commandType);
                var result = _dbCommand.ExecuteScalar();
                return result;
            }
            catch (Exception ex)
            {
                string strErrorSQL = "语句：" + sql + "；";
                if (parameters != null && parameters.Length > 0)
                {
                    strErrorSQL += "参数：";
                    foreach (var p in parameters)
                    {
                        strErrorSQL += p.Value.ToString() + "；";
                    }
                    strErrorSQL += "。";
                }
                LogError(strErrorSQL, ex);
                throw;
            }
        }

        public DbDataReader ExecuteReader(string sql)
        {
            return ExecuteReader(sql, null, CommandType.Text);
        }

        public DbDataReader ExecuteReader(string sql, DbParameter[] parameters)
        {
            return ExecuteReader(sql, parameters, CommandType.Text);
        }

        public DbDataReader ExecuteReader(string sql, DbParameter[] parameters, CommandType commandType)
        {
            try
            {
                InitExecute(sql, parameters, commandType);
                return _dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                string strErrorSQL = "语句：" + sql + "；";
                if (parameters != null && parameters.Length > 0)
                {
                    strErrorSQL += "参数：";
                    foreach (var p in parameters)
                    {
                        strErrorSQL += p.Value.ToString() + "；";
                    }
                    strErrorSQL += "。";
                }
                LogError(strErrorSQL, ex);
                throw;
            }
        }

        /// <summary>
        /// 初始化DbCommand，DbCommand.Connection尚未open
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        private void InitExecute(string sql, DbParameter[] parameters, CommandType commandType)
        {
            LogSQL(sql, parameters);
            _dbCommand.Parameters.Clear();
            _dbCommand.CommandText = sql;
            _dbCommand.CommandType = commandType;
            if (!(parameters == null || parameters.Length == 0))
            {
                _dbCommand.Parameters.AddRange(parameters);
            }
        }

        /// <summary>
        /// 输出SQL语句
        /// </summary>
        /// <param name="sql"></param>
        private void LogSQL(string sql, DbParameter[] parameters)
        {
            string log = "语句：" + sql + "；";
            if (parameters != null && parameters.Length > 0)
            {
                foreach (var param in parameters)
                {
                    log += "参数：" + param.ParameterName + "，值：" + (param.Value == null ? "" : param.Value.ToString()) + "；";
                }
            }
        }

        #region 静态方法
        /// <summary>
        /// 创建一个DbConnection
        /// </summary>
        /// <param name="providerType"></param>
        /// <returns></returns>
        public static DbConnection CreateDbConnection(DbProviderType providerType)
        {
            var providerFactory = DataBaseProviderFactory.GetDbProviderFactory(providerType);
            if (providerFactory == null)
            {
                throw new ArgumentException("Can't load DbProviderFactory for given value of providerType");
            }
            return providerFactory.CreateConnection();
        }

        /// <summary>
        /// 创建一个DbCommand
        /// </summary>
        /// <param name="providerType"></param>
        /// <returns></returns>
        public static DbCommand CreateDbCommand(DbProviderType providerType)
        {
            var providerFactory = DataBaseProviderFactory.GetDbProviderFactory(providerType);
            if (providerFactory == null)
            {
                throw new ArgumentException("Can't load DbProviderFactory for given value of providerType");
            }
            return providerFactory.CreateCommand();
        }

        /// <summary>
        /// 创建一个DbParameter
        /// </summary>
        /// <param name="providerType"></param>
        /// <returns></returns>
        public static DbParameter CreateDbParameter(DbProviderType providerType)
        {
            var providerFactory = DataBaseProviderFactory.GetDbProviderFactory(providerType);
            if (providerFactory == null)
            {
                throw new ArgumentException("Can't load DbProviderFactory for given value of providerType");
            }
            return providerFactory.CreateParameter();
        }

        /// <summary>
        /// 创建一个DbParameter数组
        /// </summary>
        /// <param name="providerType"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public static DbParameter[] CreateDbParameters(DbProviderType providerType, int count = 1)
        {
            if (count < 1) return null;
            var param = new DbParameter[count];
            for (var i = 0; i < count; i++)
            {
                param[i] = CreateDbParameter(providerType);
            }
            return param;
        }
        #endregion

        private class CommandObject
        {
            /// <summary>
            /// 1：增加，2：编辑，3：删除
            /// </summary>
            public int SQLType;

            public string SQL;

            public DbParameter[] Params;

            public object Source;
        }
    }
}
